﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ArbolandiaBL
{
    internal class SubcategoryProvider
    {
        private ArbolandiaDBDataContext _db;

        public SubcategoryProvider()
        {
            _db = new ArbolandiaDBDataContext();

        }
        internal void InsertUpdateSubCategory(Product_SubCategory cat)
        {
            var query = _db.Product_SubCategory.Where(w => w.Id == cat.Id);
            if (query.Count() > 0)
            {
                query.First().Product_CategoryId = cat.Product_CategoryId;
                query.First().FullDescription = cat.FullDescription;
                query.First().MetaKeywords = cat.MetaKeywords;
                query.First().Name = cat.Name;
                //query.First().PictureGuidId = cat.PictureGuidId;
                query.First().VideoUrl = cat.VideoUrl;
            }
            else
            {
                _db.Product_SubCategory.InsertOnSubmit(cat);
            }
            _db.SubmitChanges();
        }
        internal void DeleteSubCategory(int id)
        {
            DeletePictureSubcategory(id);
            DeleteSubCategoryGroup(id);
            var query = _db.Product_SubCategory.Where(w => w.Id == id);
            if (query.Count() > 0) { _db.Product_SubCategory.DeleteOnSubmit(query.First()); _db.SubmitChanges(); }
        }
        internal void InsertSubCategoryGroup(Product_SubCategoryGroup sub)
        {
            var query = _db.Product_SubCategoryGroup.Where(w => w.Product_SubCategoryId == sub.Product_SubCategoryId
                && w.Product_ProductId == sub.Product_ProductId);
            if (query.Count() <= 0) { _db.Product_SubCategoryGroup.InsertOnSubmit(sub); _db.SubmitChanges(); }
        }
        internal void DeleteSubCategoryGroup(int productid, int subcategoryid)
        {
            var query = _db.Product_SubCategoryGroup.Where(w => w.Product_SubCategoryId == subcategoryid
                 && w.Product_ProductId == productid);
            if (query.Count() > 0) { _db.Product_SubCategoryGroup.DeleteOnSubmit(query.First()); _db.SubmitChanges(); }
        }
        internal void InsertUpdateProductOnSubcategory(Product_SubCategoryGroup sub)
        {
            var queryDelete = _db.Product_SubCategoryGroup.Where(w => w.Product_ProductId == sub.Product_ProductId);
            if (queryDelete.Count() > 0)
            {
                _db.Product_SubCategoryGroup.DeleteAllOnSubmit(queryDelete.ToList());
                _db.SubmitChanges();
            }
            //Agregamos el nuevo
            _db.Product_SubCategoryGroup.InsertOnSubmit(sub);
            _db.SubmitChanges();
        }
        internal Product_SubCategory GetSubcateforyForProduct(int idproduct)
        {
            var id = 0; var res = new Product_SubCategory();
            var query = _db.Product_SubCategoryGroup.Where(w => w.Product_ProductId == idproduct);
            id = query.Count() > 0 ? query.First().Product_SubCategoryId : 0;
            res = GetSubcategoryId(id);
            return res;
        }
        internal void DeleteSubCategoryGroup(int subcategoryid)
        {
            var query = _db.Product_SubCategoryGroup.Where(w => w.Product_SubCategoryId == subcategoryid);
            if (query.Count() > 0) { _db.Product_SubCategoryGroup.DeleteOnSubmit(query.First()); _db.SubmitChanges(); }
        }
        internal List<Product_SubCategory> GetSubcategorys()
        {
            var res = new List<Product_SubCategory>();
            var query = from s in _db.Product_SubCategory
                        join c in _db.Product_Category on s.Product_CategoryId equals c.Id
                        select new
                        {
                            CategoryName = c.Name,
                            FullDescription = s.FullDescription,
                            Id = s.Id,
                            MetaKeywords = s.MetaKeywords,
                            Name = s.Name,
                            PictureGuidId = s.PictureGuidId,
                            PrecioMasBajo = s.PrecioMasBajo,
                            Product_CategoryId = s.Product_CategoryId,
                            VideoUrl = s.VideoUrl
                        };

            foreach (var i in query.ToList())
            {
                res.Add(new Product_SubCategory
                {
                    CategoryName = i.CategoryName,
                    FullDescription = i.FullDescription,
                    Id = i.Id,
                    MetaKeywords = i.MetaKeywords,
                    Name = i.Name,
                    PictureGuidId = i.PictureGuidId,
                    PrecioMasBajo = i.PrecioMasBajo,
                    Product_CategoryId = i.Product_CategoryId,
                    VideoUrl = i.VideoUrl

                });
            }
            return res;
        }
        internal List<Product_SubCategory> GetSubcategorys(Int32 categoryId)
        {
            var res = new List<Product_SubCategory>();
            var query = from s in _db.Product_SubCategory
                        join c in _db.Product_Category on s.Product_CategoryId equals c.Id
                        where c.Id == categoryId
                        select new
                        {
                            CategoryName = c.Name,
                            FullDescription = s.FullDescription,
                            Id = s.Id,
                            MetaKeywords = s.MetaKeywords,
                            Name = s.Name,
                            PictureGuidId = s.PictureGuidId,
                            PrecioMasBajo = s.PrecioMasBajo,
                            Product_CategoryId = s.Product_CategoryId,
                            VideoUrl = s.VideoUrl,
                            ThumbnailGuidId = s.ThumbnailGuidId
                        };

            foreach (var i in query.ToList())
            {
                res.Add(new Product_SubCategory
                {
                    CategoryName = i.CategoryName,
                    FullDescription = i.FullDescription,
                    Id = i.Id,
                    MetaKeywords = i.MetaKeywords,
                    Name = i.Name,
                    PictureGuidId = i.PictureGuidId,
                    PrecioMasBajo = i.PrecioMasBajo,
                    Product_CategoryId = i.Product_CategoryId,
                    VideoUrl = i.VideoUrl,
                    ThumbnailGuidId = i.ThumbnailGuidId

                });
            }
            return res;
        }
        internal Product_SubCategory GetSubcategoryId(int id)
        {
            var query = _db.Product_SubCategory.Where(w => w.Id == id);
            return query.Count() > 0 ? query.FirstOrDefault<Product_SubCategory>() : new Product_SubCategory();
        }
        internal void UpdatePictureGuidSubCategory(Int32 subcategoryid, Guid guid, Guid guidthumb)
        {

            var query = _db.Product_SubCategory.Where(w => w.Id == subcategoryid);
            if (query.Count() > 0)
            {
                query.First().PictureGuidId = guid;
                query.First().ThumbnailGuidId = guidthumb;
                _db.SubmitChanges();
            }
        }
        internal void InsertPictureSubcategory(Int32 subcategoryid, Guid guid, Picture picture, Guid guidthumb, Picture picturethumb)
        {
            //Guardamos la imagen y la damos de alta en arb_picture
            picture.GuidId = guid;
            if (picture.LinkUrl != null && picture.LinkUrl != "")
            {
                picture.Url = picture.LinkUrl.StartsWith("http://") ? picture.LinkUrl : ("http://" + picture.LinkUrl);
            }
            _db.Picture.InsertOnSubmit(picture);
            _db.Picture.InsertOnSubmit(picturethumb);
            _db.SubmitChanges();

            //actualizamos el guid en la tabla de subcategorias
            UpdatePictureGuidSubCategory(subcategoryid, guid, guidthumb);
        }
        internal List<object> GetPictureSubcategory(Int32 subcategoryid)
        {
            var res = new List<object>();

            var query = from s in _db.spGetPictureSubcategory(subcategoryid)
                        select new
                        {
                            GuidId = s.GuidId,
                            Name = s.Name,
                            url = s.url
                        };
            foreach (var item in query)
            {
                res.Add(new
                {
                    GuidId = item.GuidId,
                    Name = item.Name,
                    url = item.url
                });
            }
            return res;
        }
        internal void DeletePictureSubcategory(Int32 subcategoryid)
        {
            var query = from s in _db.Product_SubCategory
                        from p in _db.Picture
                        where s.Id == subcategoryid && (s.PictureGuidId == p.GuidId || s.ThumbnailGuidId == p.GuidId)
                        select p;
            if (query.Count() > 0)
            {
                _db.Picture.DeleteAllOnSubmit(query.ToList());
                _db.SubmitChanges();
            }

        }
    }
}
